package com.df.springmvc.model.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.persistence.ParameterMode;

import org.hibernate.SQLQuery;
import org.hibernate.procedure.ProcedureCall;
import org.hibernate.procedure.ProcedureOutputs;
import org.springframework.stereotype.Repository;
import com.common.bean.SQLInsertBean;
import com.common.bean.SQLUpdateBean;
import com.df.springmvc.common.bean.FieldBean;
import com.df.springmvc.supper.SupperDao;
import com.google.gson.Gson;

/**
 * 本类只做数据库操作处理
 * @author David
 */
@Repository
public class PageDao extends SupperDao{
	String getUser;
	String createTableSql="";
	
	public ArrayList<Object[]> getList() throws Exception{
		try{
			return querySqlToSQLResultBean("select '0',guid,id,code,name,type,tier1,tier2,tier3 from tmp_ModelInfo").getList(Object[].class);
		}catch(Exception e){
			// TODO: handle exception
			e.printStackTrace();
			createTable();
			return querySqlToSQLResultBean("select * from tmp_ModelInfo").getList(Object[].class);
		}finally{
		}
	}
	public void createTable() throws Exception{
		ArrayList<FieldBean> fieldBeans=new ArrayList<FieldBean>();
		fieldBeans.add(new FieldBean("guid","guid","varchar",50));
		fieldBeans.add(new FieldBean("status","status","varchar",50));
		fieldBeans.add(new FieldBean("createBy","createBy","varchar",50));
		fieldBeans.add(new FieldBean("createTime","createTime","date",0));
		fieldBeans.add(new FieldBean("updateby","updateby","varchar",50));
		fieldBeans.add(new FieldBean("updatetime","updatetime","date",0));
		fieldBeans.add(new FieldBean("id","id","varchar",50));
		fieldBeans.add(new FieldBean("code","code","varchar",50));
		fieldBeans.add(new FieldBean("name","name","varchar",50));
		fieldBeans.add(new FieldBean("type","type","varchar",50));
		fieldBeans.add(new FieldBean("tier1","tier1","varchar",50));
		fieldBeans.add(new FieldBean("tier2","tier2","varchar",50));
		fieldBeans.add(new FieldBean("tier3","tier3","varchar",50));
		fieldBeans.add(new FieldBean("description","description","varchar",1000));
		fieldBeans.add(new FieldBean("dpt","dpt","varchar",50));
		fieldBeans.add(new FieldBean("province","province","varchar",50));
		fieldBeans.add(new FieldBean("city","city","varchar",50));
		fieldBeans.add(new FieldBean("address ","address","varchar",1000));
		createTable("tmp_ModelInfo",fieldBeans);
	}
	/**
	 * 查询性别
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String,Object>> getType() throws Exception{
		return querySqlToSQLResultBean("select 1 as code,'电脑' as name union all  select 2 as code,'手机' ").getMapList();
	}
	public String getGetUser(){
		return getUser;
	}
	public void setGetUser(String getUser){
		this.getUser=getUser;
	}
	public boolean check(String id) throws Exception{
		// TODO Auto-generated method stub
		String sql="select * from tmp_ModelInfo where id=:id";
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("id",id);
		ArrayList<HashMap<String,Object>> list=querySqlToSQLResultBean(sql,tmp,0,0).getMapList();
		if(list.size() == 0){
			return true;
		}else{
			return false;
		}
	}
	@Override
	public Object add(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		String sql="select * from tmp_ModelInfo where id=:id";
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("id",map.get("id"));
		ArrayList<HashMap<String,Object>> list=querySqlToSQLResultBean(sql,tmp,0,0).getMapList();
		if(list.size() == 0){
			excuteSqlBean(new SQLInsertBean("tmp_ModelInfo",map));
		}else{
			throw new Exception("数据已经存在");
		}
		return super.add(map);
	}
	@Override
	public Object upd(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("guid",map.get("guid"));
		Object rs=excuteSqlBean(new SQLUpdateBean("tmp_ModelInfo",map,tmp){});
		return rs;
	}
	@Override
	public Object del(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		Object rs=updateSql("delete tmp_ModelInfo where guid  =:guid",map);
		return rs;
	}
	@Override
	public HashMap<String,Object> view(String guid) throws Exception{
		// TODO Auto-generated method stub
		String sql="select * from tmp_ModelInfo where guid=:guid";
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("guid",guid);
		ArrayList<HashMap<String,Object>> list=querySqlToSQLResultBean(sql,tmp,0,0).getMapList();
		if(list.size() == 1){
			return list.get(0);
		}else{
			throw new Exception("找不到数据");
		}
	}
	/**
	 * http://in.relation.to/2013/04/03/callable-statement-support/<br>
	 * create proc sp_getUserGroup
	 * @user varchar(20) ,@rs varchar(20) output
	 *       as
	 *       begin
	 *       if @user='admin'
	 *       set @rs='-1'
	 *       else
	 *       set @rs= '1'
	 *       end
	 * @param map
	 * @return
	 * @throws Exception
	 * @ref
	 *      http://apiwave.com/java/snippet/hibernate/hibernate-orm/6062eb67ea7f34ac66104a0b57f1edad681eaec0/hibernate-core.src.main.java.org.hibernate.internal.AbstractSessionImpl.java
	 */
	public Object execProc(HashMap<String,Object> map) throws Exception{

		String sql="{call  sp_getSingleRankData('','','','','111','6d3344aa-71be-4797-8ee9-8abcc9aec890')}";
		SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(sql);
		List list = query.list();
		System.out.println(new Gson().toJson(list));
//		String sql=" sp_getUserGroup";
//		ProcedureCall call=getSessionFactory().getCurrentSession().createStoredProcedureCall(sql);
//		call.registerParameter("user",String.class,ParameterMode.IN).bindValue("admin1");
//		call.registerParameter("rs",String.class,ParameterMode.OUT);
//		ProcedureOutputs outputs=call.getOutputs();
//		Object rs=outputs.getOutputParameterValue("rs");
//		org.apache.log4j.Logger.getLogger(getClass()).info("rs=" + rs);
		return "";
	}
}
